import pymysql
from urllib.request import urlopen
from bs4 import BeautifulSoup
import re
import datetime
import random
import logging


DATABASE_NAME = "scraping"
TABLE_PAGES = "pages"
COLUMN_TITLE = "title"
COLUMN_CONTENT = "content"
COLUMN_URL = "url"

MAX_LINK_SIZE = 10

def connection():
    return pymysql.connect(host="localhost",port=3306,user="root",password="yangsu87852554")

def init():
    conn = connection()
    cur = conn.cursor()
    cur.execute("create database if not exists %s"%DATABASE_NAME)
    cur.execute("use %s" % DATABASE_NAME)
    cur.execute("create table if not exists %s(id int auto_increment PRIMARY KEY,%s varchar(200),%s TEXT,%s varchar(2000))" % (TABLE_PAGES,COLUMN_TITLE,COLUMN_CONTENT,COLUMN_URL))
    cur.execute("ALTER DATABASE %s CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci"%DATABASE_NAME)
    cur.execute("ALTER TABLE %s CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"%TABLE_PAGES)
    cur.execute("ALTER TABLE pages CHANGE %s %s VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"%(COLUMN_TITLE,COLUMN_TITLE))
    cur.execute("ALTER TABLE pages CHANGE %s %s TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"%(COLUMN_CONTENT,COLUMN_CONTENT))
    cur.execute("ALTER TABLE pages CHANGE %s %s VARCHAR(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"%(COLUMN_URL,COLUMN_URL))
    cur.close()
    conn.close()

def insertRow(title,content,url):
    conn = connection()
    cur = conn.cursor()
    cur.execute("use %s" % DATABASE_NAME)
    cur.execute("insert into %s(%s,%s,%s) values(\"%s\",\"%s\",\"%s\")" % (TABLE_PAGES,COLUMN_TITLE,COLUMN_CONTENT,COLUMN_URL,title,content,url))
    conn.commit()
    cur.close()
    conn.close()

def insertList(list):
    conn = connection()
    cur = conn.cursor()
    cur.execute("use %s" % DATABASE_NAME)
    for item in list:
        logging.info("%s,%s,%s"%(item[0],item[1],item[2]))
        try:
            cur.execute("insert into %s(%s,%s,%s) values(\"%s\",\"%s\",\"%s\")" % (TABLE_PAGES,COLUMN_TITLE,COLUMN_CONTENT,COLUMN_URL,item[0],item[1],item[2]))
        except Exception as e:
            logging.error("%s,%s,%s"%(item[0],item[1],item[2]),e)
    conn.commit()
    cur.close()
    conn.close()

def query():
    conn = connection()
    cur = conn.cursor()
    cur.execute("use %s" % DATABASE_NAME)
    cur.execute("select * from %s" % TABLE_PAGES)
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data

def drop():
    conn = connection()
    cur = conn.cursor()
    cur.execute("use %s" % DATABASE_NAME)
    cur.execute("drop table if exists %s" % TABLE_PAGES)
    cur.close()
    conn.close()

def createData():
    myList = list()
    for i in range(3):
        myList.append(("title" + str(i),"content" + str(i),"url" + str(i)))
    return myList

def createLink(baseLink):
    html = urlopen("http://en.wikipedia.org"+baseLink)
    soup = BeautifulSoup(html,"html.parser")
    title = soup.find("h1").get_text()
    content = soup.find("div", {"id":"mw-content-text"}).find("p").get_text()
    return title,content,soup.find("div",{"id":"bodyContent"}).findAll("a",href=re.compile("^(/wiki/)((?!:).)*$"))

def createLinks():
    index = 0
    linkSet = set()
    random.seed(datetime.datetime.now())
    myList = list()
    baseUrl = "/wiki/Kevin_Bacon"
    title,content,links = createLink(baseUrl)
    logging.info("%s,%s"%(title,content))
    myList.append((title,content,baseUrl))
    while not(links is None)  and len(links) > 0 and index < MAX_LINK_SIZE:
        baseUrl = links[random.randint(0,len(links) - 1)].attrs["href"]
        if not baseUrl in linkSet:
            try:
                title,content,links = createLink(baseUrl)
                logging.info("%s,%s"%(title,content))
                myList.append((title,content,baseUrl))
                index += 1
            except Exception as e:
                logging.error(baseUrl,e)


    return myList

def insert(data):
    if isinstance(data,list):
        insertList(data)
    elif isinstance(data,tuple):
        insertRow(data[0], data[1])

def main():
    LOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"
    logging.basicConfig(level=logging.DEBUG, format=LOG_FORMAT)


    s = input("0:drop;\r\n1:insert;\r\nothers:query;\r\n>>")
    if int(s) == 1:
        init()
        myList = createLinks()
        insert(myList)
    elif int(s) == 0:
        drop()
    else:
        data = query()
        for item in data:
            print(item)

main()





